﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WcfService.Util;
using WcfService.DTO;
using System.Data;
using System.Data.OleDb;

namespace WcfService.DAO
{
    public class NguoiDungDAO : DataProvider
    {
        public NguoiDungDAO()
        {
            connect();
        }
        public NguoiDungDTO[] getAll()
        {
            string query = "SELECT * FROM NguoiDung";
            adapter = new OleDbDataAdapter(query, connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            NguoiDungDTO[] arr;

            DataTable dt = dataset.Tables[0];
            int i, n = dt.Rows.Count;
            arr = new NguoiDungDTO[n];
            for (i = 0; i < n; i++)
            {
                object nd = GetDataFromDataRow(dt, i);
                arr[i] = (NguoiDungDTO)nd;
            }
            return arr;
        }
        public NguoiDungDTO[] getUser(string ma)
        {
            string query = "SELECT * FROM NguoiDung WHERE MaNguoiDung='" + ma + "'";
            adapter = new OleDbDataAdapter(query, connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            NguoiDungDTO[] arr;

            DataTable dt = dataset.Tables[0];
            int i, n = dt.Rows.Count;
            arr = new NguoiDungDTO[n];
            for (i = 0; i < n; i++)
            {
                object nd = GetDataFromDataRow(dt, i);
                arr[i] = (NguoiDungDTO)nd;
            }
            return arr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            NguoiDungDTO nd = new NguoiDungDTO();
            nd.MaNguoiDung = dt.Rows[i]["MaNguoiDung"].ToString();
            nd.Email = dt.Rows[i]["Email"].ToString();
            nd.Ten = dt.Rows[i]["Ten"].ToString();
            nd.Quyen = int.Parse(dt.Rows[i]["Quyen"].ToString());
            nd.TinhTrang = int.Parse(dt.Rows[i]["TinhTrang"].ToString());

            return (object)nd;
        }

        public void insert(string ma, string email, string ten, int quyen, int tinhTrang)
        {
            string insertCommand = "INSERT INTO NguoiDung VALUES('" +
                  ma + "','" +
                                   email + "','" +
                                   ten + "', " +
                                   quyen + "," +
                                   tinhTrang + ")";

            executeNonQuery(insertCommand);
            disconnect();
        }

        public void insertUser(NguoiDungDTO user)
        {
            string insertCommand = "INSERT INTO NguoiDung VALUES('" +
                  user.MaNguoiDung + "','" +
                                   user.Email + "','" +
                                   user.Ten + "', " +
                                   user.Quyen + "," +
                                   user.TinhTrang + ")";

            executeNonQuery(insertCommand);
            disconnect();
        }

        public void delete(string ma)
        {
            string deleteCommand = "UPDATE NguoiDung SET TinhTrang = 0 WHERE MaNguoiDung LIKE '" + ma + "'";
            executeNonQuery(deleteCommand);
            disconnect();
        }

        public void deleteRestore(string ma, string tinhTrang)
        {
            int trash = int.Parse(tinhTrang);
            string deleteCommand = "";
            if (trash == 1)
            {
                deleteCommand = "UPDATE NguoiDung SET TinhTrang = 0 WHERE MaNguoiDung LIKE '" + ma + "'";
            }
            else {
                deleteCommand = "UPDATE NguoiDung SET TinhTrang = 1 WHERE MaNguoiDung LIKE '" + ma + "'";
            }
            executeNonQuery(deleteCommand);
            disconnect();
        }
        
        public void update(string ma, string email, string ten, int quyen, int tinhTrang)
        {
            string updateCommand = "UPDATE NguoiDung SET Ten = '" + ten + "',"
                + "Quyen = " + quyen + ","
                + "TinhTrang = " + tinhTrang
                + "WHERE Email LIKE '" + email + "' AND MaNguoiDung LIKE '" + ma + "'";
            executeNonQuery(updateCommand);
            disconnect();
        }
        public void updateUser(NguoiDungDTO user)
        {
            string updateCommand = "UPDATE NguoiDung SET Ten = '" + user.Ten + "',"
                + "Quyen = " + user.Quyen + ","
                + "TinhTrang = " + user.TinhTrang
                + "WHERE Email = '" + user.Email + "' AND MaNguoiDung = '" + user.MaNguoiDung + "'";
            executeNonQuery(updateCommand);
            disconnect();
        }
    }
}